package com.example.demo.utils;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.StatementVisitorAdapter;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

public class JSqlParserUtils {

    private static final List<String> sqlList = new ArrayList<>();

    public static void main(String[] args) {
        fillSqlList();
        sqlList.forEach(sql -> {
            try {
                System.out.println("------------------------------");
                Statement statement = CCJSqlParserUtil.parse(sql);
                System.out.println("statement.getClass().getName() = " + statement.getClass().getName());
                statement.accept(new MyStatementVisitor());
                System.out.println("sql = " + statement);
                System.out.println("-----------------------------");
            } catch (JSQLParserException e) {
                throw new RuntimeException(e);
            }
        });
    }

    public static class MyStatementVisitor extends StatementVisitorAdapter<Void> {

        private static final int i = 0;
        @Override
        public <S> Void visit(Select select, S context) {
            if (select instanceof PlainSelect) {
                select.accept(new MySelectVisitor(), i);
            }else if (select instanceof SetOperationList) {
                select.accept(new MySelectVisitor(), i);
            }
            return super.visit(select, context);
        }

        @Override
        public <S> Void visit(Update update, S context) {
            return super.visit(update, i);
        }

        @Override
        public <S> Void visit(Insert insert, S context) {
            return super.visit(insert, i);
        }

        @Override
        public <S> Void visit(Delete delete, S context) {
            return super.visit(delete, i);
        }
    }

    public static class MySelectVisitor extends SelectVisitorAdapter<Void> {
        private int i = 0;
        @Override
        public <S> Void visit(PlainSelect plainSelect, S context) {
            i = (int) context;
            System.out.println("plainSelect = " + plainSelect);
            i++;
            if (plainSelect.getJoins() != null) {
                for (Join join : plainSelect.getJoins()) {
                    Collection<Expression> onExpressions = join.getOnExpressions();
                    System.out.println("第" + i + "层查询表名为：" + plainSelect.getFromItem().toString());
                    for (Expression onExpression : onExpressions) {
                        if (onExpression instanceof EqualsTo){
                            EqualsTo equals = (EqualsTo) onExpression;
                            System.out.println("第" + i + "层查询条件为：" + equals.getLeftExpression() + " = " + equals.getRightExpression());
                        }
                        assert onExpression instanceof AndExpression;
                        AndExpression andExpression = (AndExpression) onExpression;
                        System.out.println("第" + i + "层查询条件为：" + andExpression.getLeftExpression() + " = " + andExpression.getRightExpression());
                    }
                    i++;
                }
            }
            System.out.println("第" + i + "层查询表名为：" + plainSelect.getFromItem().toString());
            if (plainSelect.getFromItem().getAlias() != null) {
                System.out.println("第" + i + "层查询表别名：" + plainSelect.getFromItem().getAlias().getName());
            }
            System.out.println("第" + i + "层查询条件为：" + plainSelect.getWhere());
            if (plainSelect.getFromItem() instanceof ParenthesedSelect) {
                plainSelect.getFromItem().accept(new MyFromItemVisitor(), i);
            }
            return super.visit(plainSelect, context);
        }



        @Override
        public <S> Void visit(SetOperationList setOpList, S context) {
            i = (int) context;
            System.out.println("setOpList = " + setOpList);
            for (Select select : setOpList.getSelects()) {
                select.accept(this, i);
            }
            return null;
        }
    }

    public static class MyFromItemVisitor extends FromItemVisitorAdapter<Void> {

        private int i = 0;

        @Override
        public <S> Void visit(PlainSelect plainSelect, S context) {
            i = (int) context;
            plainSelect.accept(new MySelectVisitor(), i);
            return null;
        }

        @Override
        public <S> Void visit(SetOperationList setOperationList, S context) {
            i = (int) context;
            setOperationList.accept(new MySelectVisitor(), i);
            return null;
        }

        @Override
        public <S> Void visit(ParenthesedSelect select, S context) {
            i = (int) context;
            select.accept(new MySelectVisitor(), i);
            return null;
        }

        @Override
        public <S> Void visit(Table table, S context) {
            i = (int) context;
            System.out.println("第" + i + "层查询表名为：" + table.getName());
            if (table.getAlias() != null) {
                System.out.println("第" + i + "层查询表别名：" + table.getAlias());
            }
            System.out.println("第" + i + "层查询条件为：" + table);
            return super.visit(table, context);
        }
    }

    public static void fillSqlList() {
        // 测试点：列别名、条件表达式、基础表访问。
        String sql = "SELECT id AS userId, name FROM users WHERE age > 18";
        sqlList.add(sql);

        // 测试点：聚合函数、普通函数、GROUP BY、HAVING子句。
        sql = "SELECT COUNT(*), AVG(salary), UPPER(name) FROM employees GROUP BY department HAVING AVG(salary) > 10000";
        sqlList.add(sql);

        // 测试点：嵌套条件（AND/OR)、IN操作符、BETWEEN操作符。
        sql = """
                SELECT * FROM orders\s
                WHERE (status = 'SHIPPED' OR status = 'DELIVERED')\s
                  AND order_date BETWEEN '2023-01-01' AND '2023-12-31'
                  AND customer_id IN (SELECT id FROM vip_customers)""";
        sqlList.add(sql);

        // 测试点：表别名、JOIN类型（内连接、左连接）、ON条件。
        sql = """
                SELECT o.id, c.name\s
                FROM orders o\s
                JOIN customers c ON o.customer_id = c.id
                LEFT JOIN payments p ON o.id = p.order_id""";
        sqlList.add(sql);

        // 测试点：子查询作为FROM项、别名识别。
        sql = """
                SELECT tmp.*\s
                FROM (SELECT department, MAX(salary) AS max_sal FROM employees GROUP BY department) tmp
                WHERE tmp.max_sal > 50000""";
        sqlList.add(sql);

        // 测试点：多值插入、函数调用（NOW()）。
        sql = """
                INSERT INTO audit_log (user_id, action, timestamp)
                VALUES (1001, 'LOGIN', NOW()),\s
                       (1002, 'LOGOUT', NOW())""";
        sqlList.add(sql);

        // 测试点：赋值表达式、子查询条件。
        sql = """
                UPDATE employees\s
                SET salary = salary * 1.1\s
                WHERE department = 'Engineering'\s
                  AND id IN (SELECT employee_id FROM performance WHERE rating = 'A')""";
        sqlList.add(sql);

        // 测试点：带JOIN的DELETE、表别名。
        sql = """
                DELETE o\s
                FROM orders o
                JOIN customers c ON o.customer_id = c.id\s
                WHERE c.country = 'US'""";
        sqlList.add(sql);

        // 测试点：WITH子句、CTE作为临时表。
        sql = """
                WITH regional_sales AS (
                    SELECT region, SUM(amount) AS total_sales
                    FROM orders
                    GROUP BY region
                )
                SELECT * FROM regional_sales WHERE total_sales > 100000""";
        sqlList.add(sql);

        // 测试点：UNION、ORDER BY、LIMIT/OFFSET。
        sql = """
                SELECT name FROM active_users
                UNION ALL
                SELECT name FROM inactive_users
                ORDER BY name LIMIT 10 OFFSET 20""";
        sqlList.add(sql);

        // 测试点：CASE条件分支、结果别名。
        sql = """
                SELECT\s
                  name,
                  CASE\s
                    WHEN age < 20 THEN 'Teen'
                    WHEN age BETWEEN 20 AND 65 THEN 'Adult'
                    ELSE 'Senior'
                  END AS age_group
                FROM users""";
        sqlList.add(sql);

        // 测试点：窗口函数（RANK()）、OVER子句、分区排序。
        sql = """
                SELECT\s
                  department,
                  employee_name,
                  salary,
                  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
                FROM employees""";
        sqlList.add(sql);

        // 测试点：JSON函数解析、路径表达式。
        sql = "SELECT id, JSON_EXTRACT(profile, '$.address.city') AS city FROM users";
        sqlList.add(sql);

        sql = """
                    SELECT
                      IF(u.user_type = 1, u.user_type, IF(ur.id, 2, 0)) AS user_type,
                      IFNULL(bl.id, 0) AS black,
                      ru.*,
                      r.room_no r_room_no,
                      r.room_type r_room_type,
                      r.lock_id r_lock_id,
                      rb.bed_no rb_bed_no,
                      rb.id rb_id,
                      rt.id rt_id,
                      rt.title rt_title,
                      rt.price rt_price,
                      rt.type rt_type,
                      o.amount o_amount,
                      o.pay_way o_pay_way,
                      o.STATUS o_status,
                      o.order_no o_order_no /*,o.user_id*/
                    FROM
                      room_user ru
                      INNER JOIN room r ON r.state = 1
                      AND r.id = ru.room_id
                      LEFT JOIN sys_inst si ON si.inst_id = r.inst_id
                      LEFT JOIN room_bed rb ON rb.id = ru.bed_id
                      LEFT JOIN room_type rt ON rt.id = r.room_type
                      LEFT JOIN room_group rg ON rg.id = r.room_group
                      LEFT JOIN USER u ON u.identity_number = ru.idcard
                      LEFT JOIN user_relation ur ON ur.identity_number = ru.idcard
                      AND ur.identity_type = ru.card_type
                      LEFT JOIN blacklist bl ON bl.identity_number = ru.idcard
                      AND bl.identity_type = ru.card_type
                      LEFT JOIN orders o ON o.id = ru.order_id
                      LEFT JOIN user_card uc ON uc.user_id = u.id
                      AND uc.state = 1
                    WHERE
                      ru.STATUS = 1
                      AND r.inst_id = 201 /**/
                      group by ru.id
                    ORDER BY
                      ru.id DESC\
                """;
        sqlList.add(sql);
    }

}
